<!DOCTYPE html>
<html>
  <head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport">
  <meta name="description" content="刘清政">
  <meta name="keyword" content="hexo-theme">
  
    <link rel="shortcut icon" href="/css/images/logo.png">
  
  <title>
    
      python/Django框架/9-模型层-多表操作 | Justin-刘清政的博客
    
  </title>
  <link href="//cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/tomorrow.min.css" rel="stylesheet">
  
<link rel="stylesheet" href="/css/style.css">

  
    
<link rel="stylesheet" href="/css/plugins/gitment.css">

  
  <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/geopattern/1.2.3/js/geopattern.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.js"></script>
  
    
<script src="/js/qrious.js"></script>

  
  
    
<script src="/js/gitment.js"></script>

  
  

  
<meta name="generator" content="Hexo 4.2.0"></head>
<div class="wechat-share">
  <img src="/css/images/logo.png" />
</div>

  <body>
    <header class="header fixed-header">
  <div class="header-container">
    <a class="home-link" href="/">
      <div class="logo"></div>
      <span>Justin-刘清政的博客</span>
    </a>
    <ul class="right-list">
      
        <li class="list-item">
          
            <a href="/" class="item-link">主页</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/tags/" class="item-link">标签</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/archives/" class="item-link">归档</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/about/" class="item-link">关于我</a>
          
        </li>
      
    </ul>
    <div class="menu">
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
    </div>
    <div class="menu-mask">
      <ul class="menu-list">
        
          <li class="menu-item">
            
              <a href="/" class="menu-link">主页</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/tags/" class="menu-link">标签</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/archives/" class="menu-link">归档</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/about/" class="menu-link">关于我</a>
            
          </li>
        
      </ul>
    </div>
  </div>
</header>

    <div id="article-banner">
  <h2>python/Django框架/9-模型层-多表操作</h2>



  <p class="post-date">2020-09-24</p>
    <!-- 不蒜子统计 -->
    <span id="busuanzi_container_page_pv" style='display:none' class="">
        <i class="icon-smile icon"></i> 阅读数：<span id="busuanzi_value_page_pv"></span>次
    </span>
  <div class="arrow-down">
    <a href="javascript:;"></a>
  </div>
</div>
<main class="app-body flex-box">
  <!-- Article START -->
  <article class="post-article">
    <section class="markdown-content"><h2 id="一-创建模型"><a href="#一-创建模型" class="headerlink" title="一 创建模型"></a>一 创建模型</h2><p>实例：我们来假定下面这些概念，字段和关系</p>
<p>作者模型：一个作者有姓名和年龄。</p>
<p>作者详细模型：把作者的详情放到详情表，包含生日，手机号，家庭住址等信息。作者详情模型和作者模型之间是一对一的关系（one-to-one）</p>
<p>出版商模型：出版商有名称，所在城市以及email。</p>
<p>书籍模型： 书籍有书名和出版日期，一本书可能会有多个作者，一个作者也可以写多本书，所以作者和书籍的关系就是多对多的关联关系(many-to-many);一本书只应该由一个出版商出版，所以出版商和书籍是一对多关联关系(one-to-many)。</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br><span class="line">168</span><br><span class="line">169</span><br><span class="line">170</span><br><span class="line">171</span><br><span class="line">172</span><br></pre></td><td class="code"><pre><span class="line">Book</span><br><span class="line"></span><br><span class="line">   id    title   price  publish    </span><br><span class="line"></span><br><span class="line">   <span class="number">1</span>      php     <span class="number">100</span>   人民出版社  </span><br><span class="line">   <span class="number">2</span>      python  <span class="number">200</span>   老男孩出版社   </span><br><span class="line">   <span class="number">3</span>      go      <span class="number">100</span>   人民出版社  </span><br><span class="line">   <span class="number">4</span>      java    <span class="number">300</span>   人民出版社  </span><br><span class="line"></span><br><span class="line"></span><br><span class="line">为了存储出版社的邮箱，地址，在第一个表后面加字段</span><br><span class="line"></span><br><span class="line">Book</span><br><span class="line"></span><br><span class="line">   id    title   price  publish    email    addr    </span><br><span class="line"></span><br><span class="line">   <span class="number">1</span>      php     <span class="number">100</span>   人民出版社   <span class="number">111</span>      北京</span><br><span class="line">   <span class="number">2</span>      python  <span class="number">200</span>   老男孩出版社 <span class="number">222</span>      上海</span><br><span class="line">   <span class="number">3</span>      go      <span class="number">100</span>   人民出版社   <span class="number">111</span>      北京</span><br><span class="line">   <span class="number">4</span>      java    <span class="number">300</span>   人民出版社   <span class="number">111</span>      北京</span><br><span class="line">   </span><br><span class="line">这样会有大量重复的数据，浪费空间</span><br><span class="line"></span><br><span class="line"><span class="comment">####################################################################################</span></span><br><span class="line"></span><br><span class="line">一对多：一个出版社对应多本书（关联信息建在多的一方，也就是book表中）</span><br><span class="line"></span><br><span class="line">Book</span><br><span class="line"></span><br><span class="line">   id    title   price     publish_id   </span><br><span class="line"></span><br><span class="line"></span><br><span class="line">   <span class="number">1</span>      php     <span class="number">100</span>         <span class="number">1</span></span><br><span class="line">   <span class="number">2</span>      python  <span class="number">200</span>         <span class="number">1</span></span><br><span class="line">   <span class="number">3</span>      go      <span class="number">100</span>         <span class="number">2</span>  </span><br><span class="line">   <span class="number">4</span>      java    <span class="number">300</span>         <span class="number">1</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">Publish</span><br><span class="line"></span><br><span class="line">    id    name       email    addr    </span><br><span class="line">     <span class="number">1</span>    人民出版社   <span class="number">111</span>      北京       </span><br><span class="line">     <span class="number">2</span>    沙河出版社   <span class="number">222</span>      沙河</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">总结：一旦确定表关系是一对多：在多对应的表中创建关联字段（在多的表里创建关联字段）  ，publish_id</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">查询python这本书的出版社的邮箱(子查询)</span><br><span class="line"></span><br><span class="line">   select publish_id <span class="keyword">from</span> Book where title=“python”</span><br><span class="line">   select email <span class="keyword">from</span> Publish where id=<span class="number">1</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">####################################################################################</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">多对多：一本书有多个作者，一个作者出多本书</span><br><span class="line"></span><br><span class="line">Book</span><br><span class="line"></span><br><span class="line">   id    title   price     publish_id    </span><br><span class="line"></span><br><span class="line"></span><br><span class="line">   <span class="number">1</span>      php     <span class="number">100</span>         <span class="number">1</span>               </span><br><span class="line">   <span class="number">2</span>      python  <span class="number">200</span>         <span class="number">1</span></span><br><span class="line">   <span class="number">3</span>      go      <span class="number">100</span>         <span class="number">2</span>  </span><br><span class="line">   <span class="number">4</span>      java    <span class="number">300</span>         <span class="number">1</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">Author</span><br><span class="line">         id  name  age   addr</span><br><span class="line"></span><br><span class="line">         <span class="number">1</span>    alex  <span class="number">34</span>   beijing</span><br><span class="line">         <span class="number">2</span>    egon  <span class="number">55</span>   nanjing</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">Book2Author</span><br><span class="line"></span><br><span class="line">    id    book_id  author_id</span><br><span class="line">     <span class="number">1</span>       <span class="number">2</span>         <span class="number">1</span></span><br><span class="line">     <span class="number">2</span>       <span class="number">2</span>         <span class="number">2</span></span><br><span class="line"></span><br><span class="line">     <span class="number">3</span>       <span class="number">3</span>         <span class="number">2</span></span><br><span class="line"></span><br><span class="line">总结：一旦确定表关系是多对多：创建第三张关系表（创建中间表，中间表就三个字段，自己的id，书籍id和作者id） ：</span><br><span class="line">          </span><br><span class="line">          id      book_id   author_id</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment"># lqz出版过的书籍名称(子查询)</span></span><br><span class="line"></span><br><span class="line">select id <span class="keyword">from</span> Author where name=<span class="string">'lqz'</span></span><br><span class="line"></span><br><span class="line">select book_id <span class="keyword">from</span> Book2Author where  author_id=<span class="number">1</span></span><br><span class="line"></span><br><span class="line">select title <span class="keyword">from</span> Book where id =book_id</span><br><span class="line"></span><br><span class="line"><span class="comment">####################################################################################</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">一对一：对作者详细信息的扩展（作者表和作者详情表）</span><br><span class="line"></span><br><span class="line">Author</span><br><span class="line">         id  name  age     ad_id(UNIQUE) </span><br><span class="line"></span><br><span class="line">         <span class="number">1</span>    lqz  <span class="number">34</span>       <span class="number">1</span>     </span><br><span class="line">         <span class="number">2</span>    egon  <span class="number">55</span>       <span class="number">2</span>     </span><br><span class="line"></span><br><span class="line"></span><br><span class="line">AuthorDetail</span><br><span class="line"></span><br><span class="line">   id    addr      gender    tel   gf_name   author_id(UNIQUE)</span><br><span class="line">    <span class="number">1</span>   beijing    male      <span class="number">110</span>   小花           <span class="number">1</span></span><br><span class="line">    <span class="number">2</span>   nanjing    male      <span class="number">911</span>   杠娘           <span class="number">2</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">总结： 一旦确定是一对一的关系：在两张表中的任意一张表中建立关联字段＋Unique</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">====================================</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">Publish  </span><br><span class="line">Book</span><br><span class="line">Author</span><br><span class="line">AuthorDetail</span><br><span class="line">Book2Author</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">CREATE TABLE publish(</span><br><span class="line">                id INT PRIMARY KEY auto_increment ,</span><br><span class="line">                name VARCHAR (<span class="number">20</span>)</span><br><span class="line">              );</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">CREATE TABLE book(</span><br><span class="line">                id INT PRIMARY KEY auto_increment ,</span><br><span class="line">                title VARCHAR (<span class="number">20</span>),</span><br><span class="line">                price DECIMAL (<span class="number">8</span>,<span class="number">2</span>),</span><br><span class="line">                pub_date DATE ,</span><br><span class="line">                publish_id INT ,</span><br><span class="line">                FOREIGN KEY (publish_id) REFERENCES publish(id)</span><br><span class="line">              );</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">CREATE TABLE authordetail(</span><br><span class="line">                id INT PRIMARY KEY auto_increment ,</span><br><span class="line">                tel VARCHAR (<span class="number">20</span>)</span><br><span class="line">              );</span><br><span class="line"></span><br><span class="line">CREATE TABLE author(</span><br><span class="line">                id INT PRIMARY KEY auto_increment ,</span><br><span class="line">                name VARCHAR (<span class="number">20</span>),</span><br><span class="line">                age INT,</span><br><span class="line">                authordetail_id INT UNIQUE ,</span><br><span class="line">                FOREIGN KEY (authordetail_id) REFERENCES authordetail(id)</span><br><span class="line">              );</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">CREATE  TABLE book2author(</span><br><span class="line">       id INT PRIMARY KEY auto_increment ,</span><br><span class="line">       book_id INT ,</span><br><span class="line">       author_id INT ,</span><br><span class="line">       FOREIGN KEY (book_id) REFERENCES book(id),</span><br><span class="line">       FOREIGN KEY (author_id) REFERENCES author(id)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>



<p><strong>注意：关联字段与外键约束没有必然的联系（建管理字段是为了进行查询，建约束是为了不出现脏数据）</strong></p>
<p>在Models创建如下模型</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br></pre></td><td class="code"><pre><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Book</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    nid = models.AutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    name = models.CharField(max_length=<span class="number">32</span>)</span><br><span class="line">    price = models.DecimalField(max_digits=<span class="number">5</span>, decimal_places=<span class="number">2</span>)</span><br><span class="line">    publish_date = models.DateField()</span><br><span class="line">    <span class="comment"># 阅读数</span></span><br><span class="line">    <span class="comment"># reat_num=models.IntegerField(default=0)</span></span><br><span class="line">    <span class="comment"># 评论数</span></span><br><span class="line">    <span class="comment"># commit_num=models.IntegerField(default=0)</span></span><br><span class="line"></span><br><span class="line">    publish = models.ForeignKey(to=<span class="string">'Publish'</span>,to_field=<span class="string">'nid'</span>,on_delete=models.CASCADE)</span><br><span class="line">    authors=models.ManyToManyField(to=<span class="string">'Author'</span>)</span><br><span class="line">    <span class="function"><span class="keyword">def</span> <span class="title">__str__</span><span class="params">(self)</span>:</span></span><br><span class="line">        <span class="keyword">return</span> self.name</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Author</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    nid = models.AutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    name = models.CharField(max_length=<span class="number">32</span>)</span><br><span class="line">    age = models.IntegerField()</span><br><span class="line">    author_detail = models.OneToOneField(to=<span class="string">'AuthorDatail'</span>,to_field=<span class="string">'nid'</span>,unique=<span class="literal">True</span>,on_delete=models.CASCADE)</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">AuthorDatail</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    nid = models.AutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    telephone = models.BigIntegerField()</span><br><span class="line">    birthday = models.DateField()</span><br><span class="line">    addr = models.CharField(max_length=<span class="number">64</span>)</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Publish</span><span class="params">(models.Model)</span>:</span></span><br><span class="line">    nid = models.AutoField(primary_key=<span class="literal">True</span>)</span><br><span class="line">    name = models.CharField(max_length=<span class="number">32</span>)</span><br><span class="line">    city = models.CharField(max_length=<span class="number">32</span>)</span><br><span class="line">    email = models.EmailField()</span><br></pre></td></tr></table></figure>



<p>生成的表如下：</p>
<img src="https://tva1.sinaimg.cn/large/007S8ZIlgy1gj6qcrj0acj30q80pygqi.jpg" alt="image-20200928222403872" style="zoom:50%;" />

<p>注意事项：</p>
<ul>
<li>表的名称<code>myapp_modelName</code>，是根据 模型中的元数据自动生成的，也可以覆写为别的名称　　</li>
<li><code>id</code> 字段是自动添加的</li>
<li>对于外键字段，Django 会在字段名上添加<code>&quot;_id&quot;</code> 来创建数据库中的列名</li>
<li>这个例子中的<code>CREATE TABLE</code> SQL 语句使用PostgreSQL 语法格式，要注意的是Django 会根据settings 中指定的数据库类型来使用相应的SQL 语句。</li>
<li>定义好模型之后，你需要告诉Django _使用_这些模型。你要做的就是修改配置文件中的INSTALL_APPSZ中设置，在其中添加<code>models.py</code>所在应用的名称。</li>
<li>外键字段 ForeignKey 有一个 null=True 的设置(它允许外键接受空值 NULL)，你可以赋给它空值 None 。</li>
</ul>
<h2 id="二-添加表记录"><a href="#二-添加表记录" class="headerlink" title="二 添加表记录"></a>二 添加表记录</h2><h3 id="一对多的"><a href="#一对多的" class="headerlink" title="一对多的"></a>一对多的</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">方式<span class="number">1</span>:</span><br><span class="line">   publish_obj=Publish.objects.get(nid=<span class="number">1</span>)</span><br><span class="line">   book_obj=Book.objects.create(title=<span class="string">""</span>,publishDate=<span class="string">"2012-12-12"</span>,price=<span class="number">100</span>,publish=publish_obj)</span><br><span class="line">  </span><br><span class="line">方式<span class="number">2</span>:</span><br><span class="line">   book_obj=Book.objects.create(title=<span class="string">""</span>,publishDate=<span class="string">"2012-12-12"</span>,price=<span class="number">100</span>,publish_id=<span class="number">1</span>)</span><br></pre></td></tr></table></figure>

<p>核心：book_obj.publish与book_obj.publish_id是什么？ </p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">关键点:</span><br><span class="line"></span><br><span class="line"> 一 book_obj.publish&#x3D;Publish.objects.filter(id&#x3D;book_obj.publish_id).first()</span><br><span class="line"></span><br><span class="line"> 二 book_obj.authors.all()</span><br><span class="line">    关键点:book.authors.all()  # 与这本书关联的作者集合</span><br><span class="line"></span><br><span class="line">     1 book.id&#x3D;3</span><br><span class="line">     2 book_authors</span><br><span class="line">         id  book_id  author_ID</span><br><span class="line">         3      3             1</span><br><span class="line">         4      3             2</span><br><span class="line"></span><br><span class="line">     3  author</span><br><span class="line">        id   name</span><br><span class="line">        1   lqz</span><br><span class="line">        2   egon</span><br><span class="line"></span><br><span class="line"> book_obj.authors.all()    -------&gt;   [lqz,egon]</span><br></pre></td></tr></table></figure>



<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># -----一对多添加</span></span><br><span class="line">pub=Publish.objects.create(name=<span class="string">'egon出版社'</span>,email=<span class="string">'445676@qq.com'</span>,city=<span class="string">'山东'</span>)</span><br><span class="line">print(pub)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 为book表绑定和publish的关系</span></span><br><span class="line"><span class="keyword">import</span> datetime,time</span><br><span class="line">now=datetime.datetime.now().__str__()</span><br><span class="line">now = datetime.datetime.now().strftime(<span class="string">'%Y-%m-%d'</span>)</span><br><span class="line">print(type(now))</span><br><span class="line">print(now)</span><br><span class="line"><span class="comment"># 日期类型必须是日期对象或者字符串形式的2018-09-12（2018-9-12），其它形式不行</span></span><br><span class="line">Book.objects.create(name=<span class="string">'海燕3'</span>,price=<span class="number">333.123</span>,publish_date=now,publish_id=<span class="number">2</span>)</span><br><span class="line">Book.objects.create(name=<span class="string">'海3燕3'</span>,price=<span class="number">35.123</span>,publish_date=<span class="string">'2018/02/28'</span>,publish=pub)</span><br><span class="line">pub=Publish.objects.filter(nid=<span class="number">1</span>).first()</span><br><span class="line">book=Book.objects.create(name=<span class="string">'测试书籍'</span>,price=<span class="number">33</span>,publish_date=<span class="string">'2018-7-28'</span>,publish=pub)</span><br><span class="line">print(book.publish.name)</span><br><span class="line"><span class="comment"># 查询出版了红楼梦这本书出版社的邮箱</span></span><br><span class="line">book=Book.objects.filter(name=<span class="string">'红楼梦'</span>).first()</span><br><span class="line">print(book.publish.email)</span><br></pre></td></tr></table></figure>

<h3 id="多对多"><a href="#多对多" class="headerlink" title="多对多"></a>多对多</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 当前生成的书籍对象</span></span><br><span class="line">  book_obj=Book.objects.create(title=<span class="string">"追风筝的人"</span>,price=<span class="number">200</span>,publishDate=<span class="string">"2012-11-12"</span>,publish_id=<span class="number">1</span>)</span><br><span class="line">  <span class="comment"># 为书籍绑定的做作者对象</span></span><br><span class="line">  yuan=Author.objects.filter(name=<span class="string">"yuan"</span>).first() <span class="comment"># 在Author表中主键为2的纪录</span></span><br><span class="line">  egon=Author.objects.filter(name=<span class="string">"alex"</span>).first() <span class="comment"># 在Author表中主键为1的纪录</span></span><br><span class="line"></span><br><span class="line">  <span class="comment"># 绑定多对多关系,即向关系表book_authors中添加纪录</span></span><br><span class="line">  book_obj.authors.add(yuan,egon)    <span class="comment">#  将某些特定的 model 对象添加到被关联对象集合中。   =======    book_obj.authors.add(*[])</span></span><br></pre></td></tr></table></figure>



<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br></pre></td><td class="code"><pre><span class="line">book = Book.objects.filter(name=<span class="string">'红楼梦'</span>).first()</span><br><span class="line">egon=Author.objects.filter(name=<span class="string">'egon'</span>).first()</span><br><span class="line">lqz=Author.objects.filter(name=<span class="string">'lqz'</span>).first()</span><br><span class="line"><span class="comment"># 1 没有返回值，直接传对象</span></span><br><span class="line">book.authors.add(lqz,egon)</span><br><span class="line"><span class="comment"># 2 直接传作者id</span></span><br><span class="line">book.authors.add(<span class="number">1</span>,<span class="number">3</span>)</span><br><span class="line"><span class="comment"># 3 直接传列表,会打散</span></span><br><span class="line">book.authors.add(*[<span class="number">1</span>,<span class="number">2</span>])</span><br><span class="line"><span class="comment"># 解除多对多关系</span></span><br><span class="line">book = Book.objects.filter(name=<span class="string">'红楼梦'</span>).first()</span><br><span class="line"><span class="comment"># 1 传作者id</span></span><br><span class="line">book.authors.remove(<span class="number">1</span>)</span><br><span class="line"><span class="comment"># 2 传作者对象</span></span><br><span class="line">egon = Author.objects.filter(name=<span class="string">'egon'</span>).first()</span><br><span class="line">book.authors.remove(egon)</span><br><span class="line"><span class="comment">#3 传*列表</span></span><br><span class="line">book.authors.remove(*[<span class="number">1</span>,<span class="number">2</span>])</span><br><span class="line"><span class="comment">#4 删除所有</span></span><br><span class="line">book.authors.clear()</span><br><span class="line"><span class="comment"># 5 拿到与 这本书关联的所有作者，结果是queryset对象，作者列表</span></span><br><span class="line">ret=book.authors.all()</span><br><span class="line"><span class="comment"># print(ret)</span></span><br><span class="line"><span class="comment"># 6 queryset对象，又可以继续点（查询红楼梦这本书所有作者的名字）</span></span><br><span class="line">ret=book.authors.all().values(<span class="string">'name'</span>)</span><br><span class="line">print(ret)</span><br><span class="line"><span class="comment"># 以上总结：</span></span><br><span class="line"><span class="comment"># （1）</span></span><br><span class="line"><span class="comment"># book=Book.objects.filter(name='红楼梦').first()</span></span><br><span class="line"><span class="comment"># print(book)</span></span><br><span class="line"><span class="comment"># 在点publish的时候，其实就是拿着publish_id又去app01_publish这个表里查数据了</span></span><br><span class="line"><span class="comment"># print(book.publish)</span></span><br><span class="line"><span class="comment"># （2）book.authors.all()</span></span><br></pre></td></tr></table></figure>

<p>核心:book_obj.authors.all()是什么？</p>
<p><strong>多对多关系其它常用API：</strong></p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">book_obj.authors.remove()      <span class="comment"># 将某个特定的对象从被关联对象集合中去除。    ======   book_obj.authors.remove(*[])</span></span><br><span class="line">book_obj.authors.clear()       <span class="comment">#清空被关联对象集合</span></span><br><span class="line">book_obj.authors.set()         <span class="comment">#先清空再设置</span></span><br></pre></td></tr></table></figure>

<h2 id="三-基于对象的跨表查询"><a href="#三-基于对象的跨表查询" class="headerlink" title="三 基于对象的跨表查询"></a>三 基于对象的跨表查询</h2><h3 id="一对多查询（publish与book）"><a href="#一对多查询（publish与book）" class="headerlink" title="一对多查询（publish与book）"></a>一对多查询（publish与book）</h3><p>正向查询（按字段：publish）</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 查询主键为1的书籍的出版社所在的城市</span></span><br><span class="line">book_obj=Book.objects.filter(pk=<span class="number">1</span>).first()</span><br><span class="line"><span class="comment"># book_obj.publish 是主键为1的书籍对象关联的出版社对象</span></span><br><span class="line">print(book_obj.publish.city)</span><br></pre></td></tr></table></figure>

<p>反向查询（按表名：book_set）</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">publish=Publish.objects.get(name=<span class="string">"苹果出版社"</span>)</span><br><span class="line"><span class="comment">#publish.book_set.all() : 与苹果出版社关联的所有书籍对象集合</span></span><br><span class="line">book_list=publish.book_set.all()    </span><br><span class="line"><span class="keyword">for</span> book_obj <span class="keyword">in</span> book_list:</span><br><span class="line">       print(book_obj.title)</span><br></pre></td></tr></table></figure>



<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 一对多正向查询</span></span><br><span class="line"> book=Book.objects.filter(name=<span class="string">'红楼梦'</span>).first()</span><br><span class="line"> print(book.publish)<span class="comment">#与这本书关联的出版社对象</span></span><br><span class="line"> print(book.publish.name)</span><br><span class="line"> <span class="comment"># 一对多反向查询</span></span><br><span class="line"> <span class="comment"># 人民出版社出版过的书籍名称</span></span><br><span class="line"> pub=Publish.objects.filter(name=<span class="string">'人民出版社'</span>).first()</span><br><span class="line"> ret=pub.book_set.all()</span><br><span class="line"> print(ret)</span><br></pre></td></tr></table></figure>

<h3 id="一对一查询（Author-与-AuthorDetail）"><a href="#一对一查询（Author-与-AuthorDetail）" class="headerlink" title="一对一查询（Author 与 AuthorDetail）"></a>一对一查询（Author 与 AuthorDetail）</h3><p>正向查询(按字段：authorDetail)：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">egon=Author.objects.filter(name=<span class="string">"egon"</span>).first()</span><br><span class="line">print(egon.authorDetail.telephone)</span><br></pre></td></tr></table></figure>

<p>反向查询(按表名：author)：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 查询所有住址在北京的作者的姓名</span></span><br><span class="line"> </span><br><span class="line">authorDetail_list=AuthorDetail.objects.filter(addr=<span class="string">"beijing"</span>)</span><br><span class="line"><span class="keyword">for</span> obj <span class="keyword">in</span> authorDetail_list:</span><br><span class="line">     print(obj.author.name)</span><br></pre></td></tr></table></figure>

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 一对一正向查询</span></span><br><span class="line"><span class="comment"># lqz的手机号</span></span><br><span class="line">lqz=Author.objects.filter(name=<span class="string">'lqz'</span>).first()</span><br><span class="line">tel=lqz.author_detail.telephone</span><br><span class="line">print(tel)</span><br><span class="line"><span class="comment"># 一对一反向查询</span></span><br><span class="line"><span class="comment"># 地址在北京的作者姓名</span></span><br><span class="line">author_detail=AuthorDatail.objects.filter(addr=<span class="string">'北京'</span>).first()</span><br><span class="line">name=author_detail.author.name</span><br><span class="line">print(name)</span><br></pre></td></tr></table></figure>



<h3 id="多对多查询-Author-与-Book"><a href="#多对多查询-Author-与-Book" class="headerlink" title="多对多查询 (Author 与 Book)"></a>多对多查询 (Author 与 Book)</h3><p>正向查询(按字段：authors)：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 眉所有作者的名字以及手机号</span></span><br><span class="line"> </span><br><span class="line">book_obj=Book.objects.filter(title=<span class="string">"眉"</span>).first()</span><br><span class="line">authors=book_obj.authors.all()</span><br><span class="line"><span class="keyword">for</span> author_obj <span class="keyword">in</span> authors:</span><br><span class="line">     print(author_obj.name,author_obj.authorDetail.telephone)</span><br></pre></td></tr></table></figure>

<p>反向查询(按表名：book_set)：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 查询egon出过的所有书籍的名字</span></span><br><span class="line">  author_obj=Author.objects.get(name=<span class="string">"egon"</span>)</span><br><span class="line">  book_list=author_obj.book_set.all()        <span class="comment">#与egon作者相关的所有书籍</span></span><br><span class="line">  <span class="keyword">for</span> book_obj <span class="keyword">in</span> book_list:</span><br><span class="line">    print(book_obj.title)</span><br></pre></td></tr></table></figure>



<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 正向查询----查询红楼梦所有作者名称</span></span><br><span class="line">book=Book.objects.filter(name=<span class="string">'红楼梦'</span>).first()</span><br><span class="line">ret=book.authors.all()</span><br><span class="line">print(ret)</span><br><span class="line"><span class="keyword">for</span> auth <span class="keyword">in</span> ret:</span><br><span class="line">print(auth.name)</span><br><span class="line"><span class="comment"># 反向查询 查询lqz这个作者写的所有书</span></span><br><span class="line">author=Author.objects.filter(name=<span class="string">'lqz'</span>).first()</span><br><span class="line">ret=author.book_set.all()</span><br><span class="line">print(ret)</span><br></pre></td></tr></table></figure>

<p><strong>注意：</strong></p>
<p>你可以通过在 ForeignKey() 和ManyToManyField的定义中设置 related_name 的值来覆写 FOO_set 的名称。例如，如果 Article model 中做一下更改：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">publish = ForeignKey(Book, related_name=<span class="string">'bookList'</span>)</span><br></pre></td></tr></table></figure>

<p>那么接下来就会如我们看到这般:</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 查询 人民出版社出版过的所有书籍</span></span><br><span class="line"> </span><br><span class="line">publish=Publish.objects.get(name=<span class="string">"人民出版社"</span>)</span><br><span class="line">book_list=publish.bookList.all()  <span class="comment"># 与人民出版社关联的所有书籍对象集合</span></span><br></pre></td></tr></table></figure>



<h2 id="四-基于双下划线的跨表查询"><a href="#四-基于双下划线的跨表查询" class="headerlink" title="四 基于双下划线的跨表查询"></a>四 基于双下划线的跨表查询</h2><p>Django 还提供了一种直观而高效的方式在查询(lookups)中表示关联关系，它能自动确认 SQL JOIN 联系。要做跨关系查询，就使用两个下划线来链接模型(model)间关联字段的名称，直到最终链接到你想要的model 为止。</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">'''</span></span><br><span class="line"><span class="string">正向查询按字段,反向查询按表名小写用来告诉ORM引擎join哪张表</span></span><br><span class="line"><span class="string">'''</span></span><br></pre></td></tr></table></figure>

<h3 id="一对多查询"><a href="#一对多查询" class="headerlink" title="一对多查询"></a>一对多查询</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 练习:  查询苹果出版社出版过的所有书籍的名字与价格(一对多)</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 正向查询 按字段:publish</span></span><br><span class="line"></span><br><span class="line">queryResult=Book.objects.filter(publish__name=<span class="string">"苹果出版社"</span>).values_list(<span class="string">"title"</span>,<span class="string">"price"</span>)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 反向查询 按表名:book</span></span><br><span class="line">queryResult=Publish.objects.filter(name=<span class="string">"苹果出版社"</span>).values_list(<span class="string">"book__title"</span>,<span class="string">"book__price"</span>)</span><br><span class="line">查询的本质一样，就是select <span class="keyword">from</span>的表不一样</span><br></pre></td></tr></table></figure>

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 正向查询按字段，反向查询按表名小写</span></span><br><span class="line"><span class="comment"># 查询红楼梦这本书出版社的名字</span></span><br><span class="line"><span class="comment"># select * from app01_book inner join app01_publish</span></span><br><span class="line"><span class="comment"># on app01_book.publish_id=app01_publish.nid</span></span><br><span class="line">ret=Book.objects.filter(name=<span class="string">'红楼梦'</span>).values(<span class="string">'publish__name'</span>)</span><br><span class="line">print(ret)</span><br><span class="line">ret=Publish.objects.filter(book__name=<span class="string">'红楼梦'</span>).values(<span class="string">'name'</span>)</span><br><span class="line">print(ret)</span><br></pre></td></tr></table></figure>

<h3 id="多对多查询"><a href="#多对多查询" class="headerlink" title="多对多查询　"></a>多对多查询　</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 练习: 查询lqz出过的所有书籍的名字(多对多)</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 正向查询 按字段:authors:</span></span><br><span class="line">queryResult=Book.objects.filter(authors__name=<span class="string">"lqz"</span>).values_list(<span class="string">"title"</span>)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 反向查询 按表名:book</span></span><br><span class="line">queryResult=Author.objects.filter(name=<span class="string">"lqz"</span>).values_list(<span class="string">"book__title"</span>,<span class="string">"book__price"</span>)</span><br></pre></td></tr></table></figure>

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 正向查询按字段，反向查询按表名小写</span></span><br><span class="line"><span class="comment"># 查询红楼梦这本书出版社的名字</span></span><br><span class="line"><span class="comment"># select * from app01_book inner join app01_publish</span></span><br><span class="line"><span class="comment"># on app01_book.publish_id=app01_publish.nid</span></span><br><span class="line">ret=Book.objects.filter(name=<span class="string">'红楼梦'</span>).values(<span class="string">'publish__name'</span>)</span><br><span class="line">print(ret)</span><br><span class="line">ret=Publish.objects.filter(book__name=<span class="string">'红楼梦'</span>).values(<span class="string">'name'</span>)</span><br><span class="line">print(ret)</span><br><span class="line"><span class="comment"># sql 语句就是from的表不一样</span></span><br><span class="line"><span class="comment"># -------多对多正向查询</span></span><br><span class="line"><span class="comment"># 查询红楼梦所有的作者</span></span><br><span class="line">ret=Book.objects.filter(name=<span class="string">'红楼梦'</span>).values(<span class="string">'authors__name'</span>)</span><br><span class="line">print(ret)</span><br><span class="line"><span class="comment"># ---多对多反向查询</span></span><br><span class="line">ret=Author.objects.filter(book__name=<span class="string">'红楼梦'</span>).values(<span class="string">'name'</span>)</span><br><span class="line">ret=Author.objects.filter(book__name=<span class="string">'红楼梦'</span>).values(<span class="string">'name'</span>,<span class="string">'author_detail__addr'</span>)</span><br><span class="line">print(ret)</span><br></pre></td></tr></table></figure>

<h3 id="一对一查询"><a href="#一对一查询" class="headerlink" title="一对一查询"></a>一对一查询</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 查询alex的手机号</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 正向查询</span></span><br><span class="line">ret=Author.objects.filter(name=<span class="string">"lqz"</span>).values(<span class="string">"authordetail__telephone"</span>)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 反向查询</span></span><br><span class="line">ret=AuthorDetail.objects.filter(author__name=<span class="string">"lqz"</span>).values(<span class="string">"telephone"</span>)</span><br></pre></td></tr></table></figure>



<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 查询lqz的手机号</span></span><br><span class="line"><span class="comment"># 正向查</span></span><br><span class="line">ret=Author.objects.filter(name=<span class="string">'lqz'</span>).values(<span class="string">'author_detail__telephone'</span>)</span><br><span class="line">print(ret)</span><br><span class="line"><span class="comment"># 反向查</span></span><br><span class="line">ret= AuthorDatail.objects.filter(author__name=<span class="string">'lqz'</span>).values(<span class="string">'telephone'</span>)</span><br><span class="line">print(ret)</span><br></pre></td></tr></table></figure>

<h3 id="进阶练习-连续跨表"><a href="#进阶练习-连续跨表" class="headerlink" title="进阶练习(连续跨表)"></a>进阶练习(连续跨表)</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 练习: 查询人民出版社出版过的所有书籍的名字以及作者的姓名</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 正向查询</span></span><br><span class="line">queryResult=Book.objects.filter(publish__name=<span class="string">"人民出版社"</span>).values_list(<span class="string">"title"</span>,<span class="string">"authors__name"</span>)</span><br><span class="line"><span class="comment"># 反向查询</span></span><br><span class="line">queryResult=Publish.objects.filter(name=<span class="string">"人民出版社"</span>).values_list(<span class="string">"book__title"</span>,<span class="string">"book__authors__age"</span>,<span class="string">"book__authors__name"</span>)</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment"># 练习: 手机号以151开头的作者出版过的所有书籍名称以及出版社名称</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 方式1:</span></span><br><span class="line">queryResult=Book.objects.filter(authors__authorDetail__telephone__regex=<span class="string">"151"</span>).values_list(<span class="string">"title"</span>,<span class="string">"publish__name"</span>)</span><br><span class="line"><span class="comment"># 方式2:    </span></span><br><span class="line">ret=Author.objects.filter(authordetail__telephone__startswith=<span class="string">"151"</span>).values(<span class="string">"book__title"</span>,<span class="string">"book__publish__name"</span>)</span><br></pre></td></tr></table></figure>



<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># ----进阶练习，连续跨表</span></span><br><span class="line"><span class="comment"># 查询手机号以33开头的作者出版过的书籍名称以及书籍出版社名称</span></span><br><span class="line"><span class="comment"># author_datail author book publish</span></span><br><span class="line"><span class="comment"># 基于authorDatail表</span></span><br><span class="line"></span><br><span class="line">ret=AuthorDatail.objects.filter(telephone__startswith=<span class="string">'33'</span>).values(<span class="string">'author__book__name'</span>,<span class="string">'author__book__publish__name'</span>)</span><br><span class="line">print(ret)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 基于Author表</span></span><br><span class="line"> ret=Author.objects.filter(author_detail__telephone__startswith=<span class="number">33</span>).values(<span class="string">'book__name'</span>,<span class="string">'book__publish__name'</span>)</span><br><span class="line">print(ret)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 基于Book表</span></span><br><span class="line">ret=Book.objects.filter(authors__author_detail__telephone__startswith=<span class="string">'33'</span>).values(<span class="string">'name'</span>,<span class="string">'publish__name'</span>)</span><br><span class="line">print(ret)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 基于Publish表  </span></span><br><span class="line">ret=Publish.objects.filter(book__authors__author_detail__telephone__startswith=<span class="string">'33'</span>).values(<span class="string">'book__name'</span>,<span class="string">'name'</span>)</span><br><span class="line">    print(ret)</span><br></pre></td></tr></table></figure>

<h3 id="related-name"><a href="#related-name" class="headerlink" title="related_name"></a>related_name</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">publish = ForeignKey(Blog, related_name=<span class="string">'bookList'</span>)</span><br></pre></td></tr></table></figure>

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 练习: 查询人民出版社出版过的所有书籍的名字与价格(一对多)</span></span><br><span class="line"><span class="comment"># 反向查询 不再按表名:book,而是related_name:bookList</span></span><br><span class="line">queryResult=Publish.objects.filter(name=<span class="string">"人民出版社"</span>).values_list(<span class="string">"bookList__title"</span>,<span class="string">"bookList__price"</span>)</span><br></pre></td></tr></table></figure>

<p>反向查询时，如果定义了related_name ，则用related_name替换表名</p>
<h2 id="五-聚合查询与分组查询"><a href="#五-聚合查询与分组查询" class="headerlink" title="五 聚合查询与分组查询"></a>五 聚合查询与分组查询</h2><h3 id="聚合"><a href="#聚合" class="headerlink" title="聚合"></a>聚合</h3><p><code>aggregate</code>(<em>args, *</em>kwargs)</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 计算所有图书的平均价格</span></span><br><span class="line"><span class="keyword">from</span> django.db.models <span class="keyword">import</span> Avg</span><br><span class="line">Book.objects.all().aggregate(Avg(<span class="string">'price'</span>))</span><br><span class="line"><span class="comment">#&#123;'price__avg': 34.35&#125;</span></span><br></pre></td></tr></table></figure>



<p><code>aggregate()</code>是<code>QuerySet</code> 的一个终止子句，意思是说，它返回一个包含一些键值对的字典。键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。如果你想要为聚合值指定一个名称，可以向聚合子句提供它。</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">Book.objects.aggregate(average_price=Avg(<span class="string">'price'</span>))</span><br><span class="line"><span class="comment">#&#123;'average_price': 34.35&#125;</span></span><br></pre></td></tr></table></figure>



<p>如果你希望生成不止一个聚合，你可以向<code>aggregate()</code>子句中添加另一个参数。所以，如果你也想知道所有图书价格的最大值和最小值，可以这样查询：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">from</span> django.db.models <span class="keyword">import</span> Avg, Max, Min</span><br><span class="line">Book.objects.aggregate(Avg(<span class="string">'price'</span>), Max(<span class="string">'price'</span>), Min(<span class="string">'price'</span>))</span><br><span class="line"><span class="comment">#&#123;'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')&#125;</span></span><br></pre></td></tr></table></figure>

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 查询所有书籍的平均价格</span></span><br><span class="line"><span class="keyword">from</span> django.db.models <span class="keyword">import</span> Avg,Count,Max,Min</span><br><span class="line">ret=Book.objects.all().aggregate(Avg(<span class="string">'price'</span>))</span><br><span class="line"><span class="comment"># &#123;'price__avg': 202.896&#125;</span></span><br><span class="line"><span class="comment"># 可以改名字</span></span><br><span class="line">ret=Book.objects.all().aggregate(avg_price=Avg(<span class="string">'price'</span>))</span><br><span class="line"><span class="comment"># 统计平均价格和最大价格</span></span><br><span class="line">ret=Book.objects.all().aggregate(avg_price=Avg(<span class="string">'price'</span>),max_price=Max(<span class="string">'price'</span>))</span><br><span class="line"><span class="comment"># 统计最小价格</span></span><br><span class="line">ret = Book.objects.all().aggregate(avg_price=Avg(<span class="string">'price'</span>), min_price=Min(<span class="string">'price'</span>))</span><br><span class="line"><span class="comment"># 统计个数和平均价格</span></span><br><span class="line">ret = Book.objects.all().aggregate(avg_price=Avg(<span class="string">'price'</span>), max_price=Max(<span class="string">'price'</span>),count=Count(<span class="string">'price'</span>))</span><br><span class="line">ret = Book.objects.all().aggregate(avg_price=Avg(<span class="string">'price'</span>), max_price=Max(<span class="string">'price'</span>),count=Count(<span class="string">'nid'</span>))</span><br><span class="line">print(ret)</span><br></pre></td></tr></table></figure>

<h3 id="分组"><a href="#分组" class="headerlink" title="分组"></a>分组</h3><p>annotate()为调用的<code>QuerySet</code>中每一个对象都生成一个独立的统计值（统计方法用聚合函数）。</p>
<p>总结 ：跨表分组查询本质就是将关联表join成一张表，再按单表的思路进行分组查询。　</p>
<h3 id="查询练习"><a href="#查询练习" class="headerlink" title="查询练习"></a>查询练习</h3><p>练习：统计每一本书作者个数</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">from</span> django.db.models <span class="keyword">import</span> Avg, Max, Sum, Min, Max, Count</span><br><span class="line">book_list = models.Book.objects.all().annotate(author_num=Count(<span class="string">"authors"</span>))</span><br><span class="line"><span class="keyword">for</span> book <span class="keyword">in</span> book_list:</span><br><span class="line">     print(book.name)</span><br><span class="line">     print(book.author_num)</span><br><span class="line">book_list = models.Book.objects.all().annotate(author_num=Count(<span class="string">"authors"</span>)).values(<span class="string">'name'</span>,<span class="string">'author_num'</span>)</span><br><span class="line">print(book_list)</span><br></pre></td></tr></table></figure>



<p> 练习：统计每一个出版社的最便宜的书</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">publishList=Publish.objects.annotate(MinPrice=Min(<span class="string">"book__price"</span>))</span><br><span class="line"><span class="keyword">for</span> publish_obj <span class="keyword">in</span> publishList:</span><br><span class="line">    print(publish_obj.name,publish_obj.MinPrice)</span><br></pre></td></tr></table></figure>

<p>annotate的返回值是querySet，如果不想遍历对象，可以用上valuelist：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">queryResult= Publish.objects.annotate(MinPrice=Min(<span class="string">"book__price"</span>)).values_list(<span class="string">"name"</span>,<span class="string">"MinPrice"</span>)</span><br><span class="line">print(queryResult)</span><br></pre></td></tr></table></figure>

<p> 练习：统计每一本以py开头的书籍的作者个数：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">queryResult=Book.objects.filter(title__startswith=<span class="string">"Py"</span>).annotate(num_authors=Count(<span class="string">'authors'</span>))</span><br></pre></td></tr></table></figure>

<p> 练习：统计不止一个作者的图书：（作者数量大于一）</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">ret=models.Book.objects.annotate(author_num=Count(<span class="string">"authors"</span>)).filter(author_num__gt=<span class="number">1</span>).values(<span class="string">'name'</span>,<span class="string">'author_num'</span>)</span><br><span class="line">print(ret)</span><br></pre></td></tr></table></figure>

<p> 练习：根据一本图书作者数量的多少对查询集 <code>QuerySet</code>进行排序:</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Book.objects.annotate(num_authors=Count(<span class="string">'authors'</span>)).order_by(<span class="string">'num_authors'</span>)</span><br></pre></td></tr></table></figure>

<p> 练习：查询各个作者出的书的总价格:</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">ret=models.Author.objects.annotate(sum_price=Sum(<span class="string">"book__price"</span>)).values(<span class="string">"name"</span>, <span class="string">"sum_price"</span>)</span><br><span class="line">print(ret)</span><br></pre></td></tr></table></figure>

<p> 练习：查询每个出版社的名称和书籍个数</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">ret=models.Publish.objects.all().annotate(c=Count(<span class="string">'book__name'</span>)).values(<span class="string">'name'</span>,<span class="string">'c'</span>)</span><br><span class="line">print(ret)</span><br></pre></td></tr></table></figure>

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">'''</span></span><br><span class="line"><span class="string">       查询每一个部门名称以及对应的员工数</span></span><br><span class="line"><span class="string">       book:</span></span><br><span class="line"><span class="string">       id  name   price      publish</span></span><br><span class="line"><span class="string">        1   金品   11.2        1</span></span><br><span class="line"><span class="string">        2   西游   14.2        2</span></span><br><span class="line"><span class="string">        3   东游   16.2        2</span></span><br><span class="line"><span class="string">        4   北邮   19.2        3</span></span><br><span class="line"><span class="string">        </span></span><br><span class="line"><span class="string">    '''</span></span><br><span class="line">    <span class="comment"># 示例一：查询每一个出版社id，以及出书平均价格</span></span><br><span class="line">    <span class="comment"># select publish_id,avg(price) from app01_book group by publish_id;</span></span><br><span class="line">    <span class="comment"># annotate</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">    <span class="comment">#  annotate() 内写聚合函数</span></span><br><span class="line">    <span class="comment">#  values在前表示group by的字段</span></span><br><span class="line">    <span class="comment">#  values在后表示取某几个字段</span></span><br><span class="line">    <span class="comment">#  filter在前表示where</span></span><br><span class="line">    <span class="comment">#  filter在后表示having</span></span><br><span class="line">    <span class="comment"># from django.db.models import Avg, Count, Max, Min</span></span><br><span class="line">    <span class="comment"># ret=models.Book.objects.values('publish_id').annotate(avg=Avg('price')).values('publish_id','avg')</span></span><br><span class="line">    <span class="comment"># print(ret)</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># 查询出版社id大于1的出版社id，以及出书平均价格</span></span><br><span class="line">    <span class="comment">#select publish_id,avg(price) from app01_book where publish_id&gt;1 group by publish_id;</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># ret=models.Book.objects.values('publish_id').filter(publish_id__gt=1).annotate(avg=Avg('price')).values('publish_id','avg')</span></span><br><span class="line">    <span class="comment"># print(ret)</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># 查询出版社id大于1的出版社id，以及出书平均价格大于30的</span></span><br><span class="line">    <span class="comment"># select publish_id,avg(price)as aaa from app01_book where publish_id&gt;1 group by publish_id HAVING aaa&gt;30;</span></span><br><span class="line">    <span class="comment"># ret = models.Book.objects.values('publish_id').filter(publish_id__gt=1).annotate(avg=Avg('price')).filter(avg__gt=30).values(</span></span><br><span class="line">    <span class="comment">#     'publish_id', 'avg')</span></span><br><span class="line">    <span class="comment"># print(ret)</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">    <span class="comment">## 查询每一个出版社出版的书籍个数</span></span><br><span class="line">    <span class="comment"># pk 代指主键</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># ret=models.Book.objects.get(pk=1)</span></span><br><span class="line">    <span class="comment"># print(ret.name)</span></span><br><span class="line">    <span class="comment"># ret=models.Publish.objects.values('pk').annotate(count=Count('book__id')).values('name','count')</span></span><br><span class="line">    <span class="comment"># print(ret)</span></span><br><span class="line">    <span class="comment"># 如果没有指定group by的字段，默认就用基表（Publish）主键字段作为group by的字段</span></span><br><span class="line">    <span class="comment"># ret=models.Publish.objects.annotate(count=Count('book__id')).values('name','count')</span></span><br><span class="line">    <span class="comment"># print(ret)</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># 另一种方式实现</span></span><br><span class="line">    <span class="comment"># ret=models.Book.objects.values('publish').annotate(count=Count('id')).values('publish__name','count')</span></span><br><span class="line">    <span class="comment"># print(ret)</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">    <span class="comment">#查询每个作者的名字，以及出版过书籍的最高价格(建议使用分组的表作为基表)</span></span><br><span class="line">    <span class="comment"># 如果不用分组的表作为基表，数据不完整可能会出现问题</span></span><br><span class="line">    <span class="comment"># ret=models.Author.objects.values('pk').annotate(max=Max('book__price')).values('name','max')</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># ret = models.Author.objects.annotate(max=Max('book__price')).values('name', 'max')</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># ret= models.Book.objects.values('authors__id').annotate(max=Max('price')).values('authors__name','max')</span></span><br><span class="line">    <span class="comment"># print(ret)</span></span><br><span class="line"></span><br><span class="line">    <span class="comment">#查询每一个书籍的名称，以及对应的作者个数</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># ret=models.Book.objects.values('pk').annotate(count=Count('authors__id')).values('name','count')</span></span><br><span class="line">    <span class="comment"># ret=models.Book.objects.annotate(count=Count('authors__id')).values('name','count')</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># ret=models.Author.objects.values('book__id').annotate(count=Count('id')).values('book__name','count')</span></span><br><span class="line">    <span class="comment">#</span></span><br><span class="line">    <span class="comment"># print(ret)</span></span><br><span class="line"></span><br><span class="line">    <span class="comment">#统计不止一个作者的图书</span></span><br><span class="line">    <span class="comment"># ret=models.Book.objects.values('pk').annotate(count=Count('authors__id')).filter(count__gt=1).values('name','count')</span></span><br><span class="line">    <span class="comment"># ret = models.Author.objects.values('book__id').annotate(count=Count('id')).filter(count__gt=1).values('book__name', 'count')</span></span><br><span class="line">    <span class="comment"># print(ret)</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># 统计价格数大于10元，作者的图书</span></span><br><span class="line">    ret = models.Book.objects.values(<span class="string">'pk'</span>).filter(price__gt=<span class="number">10</span>).annotate(count=Count(<span class="string">'authors__id'</span>)).values(<span class="string">'name'</span>,</span><br><span class="line">                                                                                                           <span class="string">'count'</span>)</span><br><span class="line">    print(ret)</span><br><span class="line"></span><br><span class="line">    <span class="comment">#统计价格数大于10元，作者个数大于1的图书</span></span><br><span class="line">    ret = models.Book.objects.values(<span class="string">'pk'</span>).filter(price__gt=<span class="number">10</span>).annotate(count=Count(<span class="string">'authors__id'</span>)).filter(count__gt=<span class="number">1</span>).values(<span class="string">'name'</span>,                                                                                                    <span class="string">'count'</span>)</span><br><span class="line">    print(ret)</span><br></pre></td></tr></table></figure>

<h2 id="六-F查询与Q查询"><a href="#六-F查询与Q查询" class="headerlink" title="六 F查询与Q查询"></a>六 F查询与Q查询</h2><h3 id="F查询"><a href="#F查询" class="headerlink" title="F查询"></a>F查询</h3><p>在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？</p>
<p>Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"># 查询评论数大于收藏数的书籍</span><br><span class="line">from django.db.models import F</span><br><span class="line">Book.objects.filter(commnetNum__lt&#x3D;F(&#39;keepNum&#39;))</span><br></pre></td></tr></table></figure>

<p>Django 支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 查询评论数大于收藏数2倍的书籍</span></span><br><span class="line">Book.objects.filter(commnetNum__lt=F(<span class="string">'keepNum'</span>)*<span class="number">2</span>)</span><br></pre></td></tr></table></figure>

<p>修改操作也可以使用F函数,比如将每一本书的价格提高30元：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Book.objects.all().update(price&#x3D;F(&quot;price&quot;)+30)</span><br></pre></td></tr></table></figure>

<h3 id="Q查询"><a href="#Q查询" class="headerlink" title="Q查询"></a>Q查询</h3><p><code>filter()</code> 等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询（例如<code>OR</code> 语句），你可以使用<code>Q 对象</code>。</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">from</span> django.db.models <span class="keyword">import</span> Q</span><br><span class="line">Q(title__startswith=<span class="string">'Py'</span>)</span><br></pre></td></tr></table></figure>

<p><code>Q</code> 对象可以使用<code>&amp;</code> 和<code>|</code> 操作符组合起来。当一个操作符在两个<code>Q</code> 对象上使用时，它产生一个新的<code>Q</code> 对象。</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">bookList=Book.objects.filter(Q(authors__name=<span class="string">"lqz"</span>)|Q(authors__name=<span class="string">"egon"</span>))</span><br></pre></td></tr></table></figure>

<p>等同于下面的SQL <code>WHERE</code> 子句：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">WHERE name =<span class="string">"lqz"</span> OR name =<span class="string">"egon"</span></span><br></pre></td></tr></table></figure>

<p>你可以组合<code>&amp;</code> 和<code>|</code> 操作符以及使用括号进行分组来编写任意复杂的<code>Q</code> 对象。同时，<code>Q</code> 对象可以使用<code>~</code> 操作符取反，这允许组合正常的查询和取反(<code>NOT</code>) 查询：</p>
<p>查询函数可以混合使用<code>Q 对象</code>和关键字参数。所有提供给查询函数的参数（关键字参数或<code>Q</code> 对象）都将”AND”在一起。但是，如果出现<code>Q</code> 对象，它必须位于所有关键字参数的前面。例如：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">bookList=Book.objects.filter(Q(publishDate__year=<span class="number">2016</span>) | Q(publishDate__year=<span class="number">2017</span>), title__icontains=<span class="string">"python"</span>)</span><br></pre></td></tr></table></figure>

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 查询评论数大于阅读数的书籍</span></span><br><span class="line"><span class="keyword">from</span> django.db.models <span class="keyword">import</span> F,Q</span><br><span class="line"><span class="comment"># select * from book where commit_num&gt;read_num;</span></span><br><span class="line"><span class="comment"># 这样肯定是不行的</span></span><br><span class="line"><span class="comment"># Book.objects.filter(commit_num__gt=read_num)</span></span><br><span class="line">ret=Book.objects.filter(commit_num__gt=F(<span class="string">'reat_num'</span>))</span><br><span class="line">print(ret)</span><br><span class="line"><span class="comment"># 把所有书籍的价格加10</span></span><br><span class="line">Book.objects.all().update(price=F(<span class="string">'price'</span>)+<span class="number">10</span>)</span><br><span class="line"><span class="comment"># ----Q函数，描述一个与，或，非的关系</span></span><br><span class="line"><span class="comment"># 查询名字叫红楼梦或者价格大于100的书</span></span><br><span class="line">ret=Book.objects.filter(Q(name=<span class="string">'红楼梦'</span>)|Q(price__gt=<span class="number">100</span>))</span><br><span class="line">print(ret)</span><br><span class="line"><span class="comment"># 查询名字叫红楼梦和价格大于100的书</span></span><br><span class="line">ret = Book.objects.filter(Q(name=<span class="string">'红楼梦'</span>) &amp; Q(price__gt=<span class="number">100</span>))</span><br><span class="line">print(ret)</span><br><span class="line"><span class="comment"># # 等同于</span></span><br><span class="line">ret2=Book.objects.filter(name=<span class="string">'红楼梦'</span>,price__gt=<span class="number">100</span>)</span><br><span class="line">print(ret2)</span><br><span class="line"><span class="comment"># 也可以Q套Q</span></span><br><span class="line"><span class="comment"># 查询名字叫红楼梦和价格大于100  或者 nid大于2</span></span><br><span class="line">ret=Book.objects.filter((Q(name=<span class="string">'红楼梦'</span>) &amp; Q(price__gt=<span class="number">100</span>))|Q(nid__gt=<span class="number">2</span>))</span><br><span class="line">print(ret)</span><br><span class="line"><span class="comment"># ----非</span></span><br><span class="line">ret=Book.objects.filter(~Q(name=<span class="string">'红楼梦'</span>))</span><br><span class="line">print(ret)</span><br><span class="line"><span class="comment"># Q和键值对联合使用，但是键值对必须放在Q的后面(描述的是一个且的关系)</span></span><br><span class="line"><span class="comment"># 查询名字不是红楼梦，并且价格大于100的书</span></span><br><span class="line">ret=Book.objects.filter(~Q(name=<span class="string">'红楼梦'</span>),price__gt=<span class="number">100</span>)</span><br><span class="line">print(ret)</span><br></pre></td></tr></table></figure>

<h2 id="七-查询练习"><a href="#七-查询练习" class="headerlink" title="七 查询练习"></a>七 查询练习</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line">查找所有书名里包含红楼的书</span><br><span class="line">查找出版日期是<span class="number">2017</span>年的书</span><br><span class="line">查找出版日期是<span class="number">2017</span>年的书名</span><br><span class="line">查找价格大于<span class="number">10</span>元的书</span><br><span class="line">查找价格大于<span class="number">10</span>元的书名和价格</span><br><span class="line"></span><br><span class="line">查找在北京的出版社</span><br><span class="line">查找名字以沙河开头的出版社</span><br><span class="line"></span><br><span class="line">查找作者名字里面带“小”字的作者</span><br><span class="line">查找年龄大于<span class="number">30</span>岁的作者</span><br><span class="line">查找手机号是<span class="number">155</span>开头的作者</span><br><span class="line">查找手机号是<span class="number">155</span>开头的作者的姓名和年龄</span><br><span class="line"></span><br><span class="line">查找书名是“红楼梦”的书的出版社</span><br><span class="line">查找书名是“红楼梦”的书的出版社所在的城市</span><br><span class="line">查找书名是“红楼梦”的书的出版社的名称</span><br><span class="line"></span><br><span class="line">查找书名是“红楼梦”的书的所有作者</span><br><span class="line">查找书名是“红楼梦”的书的作者的年龄</span><br><span class="line">查找书名是“红楼梦”的书的作者的手机号码</span><br><span class="line"></span><br><span class="line">查找书名是“红楼梦”的书的作者的地址</span><br><span class="line">查找书名是“红楼梦”的书的作者的邮箱</span><br></pre></td></tr></table></figure>

</section>
    <!-- Tags START -->
    
    <!-- Tags END -->
    <!-- NAV START -->
    
  <div class="nav-container">
    <!-- reverse left and right to put prev and next in a more logic postition -->
    
      <a class="nav-left" href="/python/Django%E6%A1%86%E6%9E%B6/7-%E6%A8%A1%E6%9D%BF%E5%B1%82/">
        <span class="nav-arrow">← </span>
        
          python/Django框架/7-模板层
        
      </a>
    
    
      <a class="nav-right" href="/python/Django%E6%A1%86%E6%9E%B6/2-Http%E5%8D%8F%E8%AE%AE/">
        
          python/Django框架/2-Http协议
        
        <span class="nav-arrow"> →</span>
      </a>
    
  </div>

    <!-- NAV END -->
    <!-- 打赏 START -->
    
      <div class="money-like">
        <div class="reward-btn">
          赏
          <span class="money-code">
            <span class="alipay-code">
              <div class="code-image"></div>
              <b>使用支付宝打赏</b>
            </span>
            <span class="wechat-code">
              <div class="code-image"></div>
              <b>使用微信打赏</b>
            </span>
          </span>
        </div>
        <p class="notice">点击上方按钮,请我喝杯咖啡！</p>
      </div>
    
    <!-- 打赏 END -->
    <!-- 二维码 START -->
    
      <div class="qrcode">
        <canvas id="share-qrcode"></canvas>
        <p class="notice">扫描二维码，分享此文章</p>
      </div>
    
    <!-- 二维码 END -->
    
      <!-- Gitment START -->
      <div id="comments"></div>
      <!-- Gitment END -->
    
  </article>
  <!-- Article END -->
  <!-- Catalog START -->
  
    <aside class="catalog-container">
  <div class="toc-main">
  <!-- 不蒜子统计 -->
    <strong class="toc-title">目录</strong>
    
      <ol class="toc-nav"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#一-创建模型"><span class="toc-nav-text">一 创建模型</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#二-添加表记录"><span class="toc-nav-text">二 添加表记录</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#一对多的"><span class="toc-nav-text">一对多的</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#多对多"><span class="toc-nav-text">多对多</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#三-基于对象的跨表查询"><span class="toc-nav-text">三 基于对象的跨表查询</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#一对多查询（publish与book）"><span class="toc-nav-text">一对多查询（publish与book）</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#一对一查询（Author-与-AuthorDetail）"><span class="toc-nav-text">一对一查询（Author 与 AuthorDetail）</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#多对多查询-Author-与-Book"><span class="toc-nav-text">多对多查询 (Author 与 Book)</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#四-基于双下划线的跨表查询"><span class="toc-nav-text">四 基于双下划线的跨表查询</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#一对多查询"><span class="toc-nav-text">一对多查询</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#多对多查询"><span class="toc-nav-text">多对多查询　</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#一对一查询"><span class="toc-nav-text">一对一查询</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#进阶练习-连续跨表"><span class="toc-nav-text">进阶练习(连续跨表)</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#related-name"><span class="toc-nav-text">related_name</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#五-聚合查询与分组查询"><span class="toc-nav-text">五 聚合查询与分组查询</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#聚合"><span class="toc-nav-text">聚合</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#分组"><span class="toc-nav-text">分组</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#查询练习"><span class="toc-nav-text">查询练习</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#六-F查询与Q查询"><span class="toc-nav-text">六 F查询与Q查询</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#F查询"><span class="toc-nav-text">F查询</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#Q查询"><span class="toc-nav-text">Q查询</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#七-查询练习"><span class="toc-nav-text">七 查询练习</span></a></li></ol>
    
  </div>
</aside>
  
  <!-- Catalog END -->
</main>

<script>
  (function () {
    var url = 'http://www.liuqingzheng.top/python/Django框架/9-模型层-多表操作/';
    var banner = ''
    if (banner !== '' && banner !== 'undefined' && banner !== 'null') {
      $('#article-banner').css({
        'background-image': 'url(' + banner + ')'
      })
    } else {
      $('#article-banner').geopattern(url)
    }
    $('.header').removeClass('fixed-header')

    // error image
    $(".markdown-content img").on('error', function() {
      $(this).attr('src', 'http://file.muyutech.com/error-img.png')
      $(this).css({
        'cursor': 'default'
      })
    })

    // zoom image
    $(".markdown-content img").on('click', function() {
      var src = $(this).attr('src')
      if (src !== 'http://file.muyutech.com/error-img.png') {
        var imageW = $(this).width()
        var imageH = $(this).height()

        var zoom = ($(window).width() * 0.95 / imageW).toFixed(2)
        zoom = zoom < 1 ? 1 : zoom
        zoom = zoom > 2 ? 2 : zoom
        var transY = (($(window).height() - imageH) / 2).toFixed(2)

        $('body').append('<div class="image-view-wrap"><div class="image-view-inner"><img src="'+ src +'" /></div></div>')
        $('.image-view-wrap').addClass('wrap-active')
        $('.image-view-wrap img').css({
          'width': `${imageW}`,
          'transform': `translate3d(0, ${transY}px, 0) scale3d(${zoom}, ${zoom}, 1)`
        })
        $('html').css('overflow', 'hidden')

        $('.image-view-wrap').on('click', function() {
          $(this).remove()
          $('html').attr('style', '')
        })
      }
    })
  })();
</script>


  <script>
    var qr = new QRious({
      element: document.getElementById('share-qrcode'),
      value: document.location.href
    });
  </script>



  <script>
    var gitmentConfig = "liuqingzheng";
    if (gitmentConfig !== 'undefined') {
      var gitment = new Gitment({
        id: "python/Django框架/9-模型层-多表操作",
        owner: "liuqingzheng",
        repo: "FuckBlog",
        oauth: {
          client_id: "32a4076431cf39d0ecea",
          client_secret: "94484bd79b3346a949acb2fda3c8a76ce16990c6"
        },
        theme: {
          render(state, instance) {
            const container = document.createElement('div')
            container.lang = "en-US"
            container.className = 'gitment-container gitment-root-container'
            container.appendChild(instance.renderHeader(state, instance))
            container.appendChild(instance.renderEditor(state, instance))
            container.appendChild(instance.renderComments(state, instance))
            container.appendChild(instance.renderFooter(state, instance))
            return container;
          }
        }
      })
      gitment.render(document.getElementById('comments'))
    }
  </script>




    <div class="scroll-top">
  <span class="arrow-icon"></span>
</div>
    <footer class="app-footer">
<!-- 不蒜子统计 -->
<span id="busuanzi_container_site_pv">
     本站总访问量<span id="busuanzi_value_site_pv"></span>次
</span>
<span class="post-meta-divider">|</span>
<span id="busuanzi_container_site_uv" style='display:none'>
     本站访客数<span id="busuanzi_value_site_uv"></span>人
</span>
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>



  <p class="copyright">
    &copy; 2021 | Proudly powered by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank">小猿取经</a>
    <br>
    Theme by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank" rel="noopener">小猿取经</a>
  </p>
</footer>

<script>
  function async(u, c) {
    var d = document, t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) { o.addEventListener('load', function (e) { c(null, e); }, false); }
    s.parentNode.insertBefore(o, s);
  }
</script>
<script>
  async("//cdnjs.cloudflare.com/ajax/libs/fastclick/1.0.6/fastclick.min.js", function(){
    FastClick.attach(document.body);
  })
</script>

<script>
  var hasLine = 'true';
  async("//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/highlight.min.js", function(){
    $('figure pre').each(function(i, block) {
      var figure = $(this).parents('figure');
      if (hasLine === 'false') {
        figure.find('.gutter').hide();
      }
      var lang = figure.attr('class').split(' ')[1] || 'code';
      var codeHtml = $(this).html();
      var codeTag = document.createElement('code');
      codeTag.className = lang;
      codeTag.innerHTML = codeHtml;
      $(this).attr('class', '').empty().html(codeTag);
      figure.attr('data-lang', lang.toUpperCase());
      hljs.highlightBlock(block);
    });
  })
</script>





<!-- Baidu Tongji -->

<script>
    var _baId = 'c5fd96eee1193585be191f318c3fa725';
    // Originial
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?" + _baId;
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
</script>


<script src="/js/script.js"></script>


<script src="/js/search.js"></script>


<script src="/js/load.js"></script>



  <span class="local-search local-search-google local-search-plugin" style="right: 50px;top: 70px;;position:absolute;z-index:2;">
      <input type="search" placeholder="站内搜索" id="local-search-input" class="local-search-input-cls" style="">
      <div id="local-search-result" class="local-search-result-cls"></div>
  </span>


  </body>
</html>